package com.xmsx.back.mapper.admin;

import com.xmsx.back.pojo.Graduate;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.cache.annotation.Caching;

import java.util.List;
import java.util.Map;


@Mapper
public interface GraduateMapper {
    /**
     * 根据考生号获得毕业数据信息
     *
     * @param id
     * @return
     */
    @Select("select * from graduate where id = #{id}")
    Graduate getById(@Param("id") String id);

    /**
     * 根据学号获得毕业数据信息
     *
     * @param sid
     * @return
     */
    @Select("select * from graduate where sid = #{sid}")
    Graduate getBySid(@Param("sid") String sid);

    /**
     * 设置毕业数据信息
     *
     * @param graduate
     */
    @Caching(evict = {
            @CacheEvict(cacheNames = "YearList"),
            @CacheEvict(cacheNames = "DirectionData"),
            @CacheEvict(cacheNames = "SchoolData"),
            @CacheEvict(cacheNames = "OrgKindData"),
            @CacheEvict(cacheNames = "OrgIndustryData"),
            @CacheEvict(cacheNames = "OrgNameData"),
            @CacheEvict(cacheNames = "OrgLocationData")
    })
    @Insert("insert into graduate (id,sex,nation,politics_st,school,edu_backgro,major,origin,grad_time,difficulty,department,sid,direction,org_name,org_kind,org_industry,org_location,job_type,org_address) " +
            "values (#{graduate.id}, #{graduate.sex} ," +
            "#{graduate.nation}, #{graduate.politicsSt}," +
            "#{graduate.school}, #{graduate.eduBackgro}," +
            "#{graduate.major}, #{graduate.origin}," +
            "#{graduate.gradTime}, #{graduate.difficulty}," +
            "#{graduate.department}, #{graduate.sid}," +
            "#{graduate.direction}, #{graduate.orgName} ," +
            "#{graduate.orgKind}, #{graduate.orgIndustry} ," +
            "#{graduate.orgLocation}, #{graduate.jobType} ," +
            "#{graduate.orgAddress})")
    void setGraduate(@Param("graduate") Graduate graduate);

    /**
     * 修改毕业数据信息
     *
     * @param graduate
     */
    @Caching(evict = {
            @CacheEvict(cacheNames = "YearList"),
            @CacheEvict(cacheNames = "DirectionData"),
            @CacheEvict(cacheNames = "SchoolData"),
            @CacheEvict(cacheNames = "OrgKindData"),
            @CacheEvict(cacheNames = "OrgIndustryData"),
            @CacheEvict(cacheNames = "OrgNameData"),
            @CacheEvict(cacheNames = "OrgLocationData")
    })
    @Insert("update graduate set id=#{graduate.id}, sex=#{graduate.sex}" +
            ",nation=#{graduate.nation},politics_st=#{graduate.politicsSt}" +
            ",school=#{graduate.school},edu_backgro=#{graduate.eduBackgro}" +
            ",major=#{graduate.major},origin=#{graduate.origin}" +
            ",grad_time=#{graduate.gradTime},difficulty= #{graduate.difficulty}" +
            ",department=#{graduate.department},sid=#{graduate.sid}" +
            ",direction=#{graduate.direction},org_name=#{graduate.orgName} " +
            ",org_kind=#{graduate.orgKind},org_industry=#{graduate.orgIndustry}" +
            ",org_location=#{graduate.orgLocation},job_type= #{graduate.jobType}" +
            ",org_address=#{graduate.orgAddress} where id= #{graduate.id}")
    void updateGraduate(@Param("graduate") Graduate graduate);

    /**
     * 删除毕业数据信息
     *
     * @param id
     */
    @Caching(evict = {
            @CacheEvict(cacheNames = "YearList"),
            @CacheEvict(cacheNames = "DirectionData"),
            @CacheEvict(cacheNames = "SchoolData"),
            @CacheEvict(cacheNames = "OrgKindData"),
            @CacheEvict(cacheNames = "OrgIndustryData"),
            @CacheEvict(cacheNames = "OrgNameData"),
            @CacheEvict(cacheNames = "OrgLocationData")
    })
    @Select("delete from graduate where id = #{id}")
    void deleteGraduate(@Param("id") String id);

    /**
     * 统计记录数
     *
     * @return
     */
    @Select("select count(1) from graduate")
    long count();

    @Select("select count(1) from graduate where grad_time >= #{start} and grad_time <= #{end}")
    long countByYear(int start, int end);

    @Select("select count(1) from graduate where edu_backgro = #{edu}")
    long countByEdu(String edu);

    @Select("select count(1) from graduate where grad_time >= #{start} and grad_time <= #{end} and edu_backgro = #{edu}")
    long countByYearAndEdu(int start, int end, String edu);

    @Select("select * from graduate limit #{page},#{size}")
    List<Graduate> findByPager(Map<String, Object> params);

    @Select("select * from graduate where grad_time >= #{start} and grad_time <= #{end} limit #{page},#{size}")
    List<Graduate> findByYearByPager(Map<String, Object> params);

    @Select("select * from graduate where edu_backgro = #{edu} limit #{page},#{size}")
    List<Graduate> findByEduByPager(Map<String, Object> params);

    @Select("select * from graduate where grad_time >= #{start} and grad_time <= #{end} and edu_backgro = #{edu} limit #{page},#{size}")
    List<Graduate> findByYearAndEduByPager(Map<String, Object> params);

    @Cacheable(cacheNames = "YearList")
    @Select("select distinct grad_time from graduate order by grad_time asc")
    List<Integer> getYearList();
}